package org.iuniv.adb.patent.dao;

import java.io.IOException;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.iuniv.adb.patent.adapter.DBAdapter;
import org.iuniv.adb.patent.common.Constants;
import org.iuniv.adb.patent.common.StringUtils;
import org.iuniv.adb.patent.model.Patent;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;

/**
 * DAO Implementation for PatentDao interface
 *
 * @author Phuc
 */
public class PatentDaoImpl implements PatentDao {

    /* (non-Javadoc)
     * @see org.iuniv.adb.patent.dao.PatentDao#getPatent(java.lang.String)
     */
    @Override
    public Patent getPatent(String patNumber) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Patent patent = null;

        try {
            conn = DBAdapter.getConnection();
            String dbName = DBAdapter.getDBName();
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.append("SELECT PATENTNUMBER");
            sbQuery.append(", TITLE");
            sbQuery.append(", ABSTRACT");
            sbQuery.append(", INVENTORS");
            sbQuery.append(", EXAMINERS");
            sbQuery.append(", ASSIGNEES");
            sbQuery.append(", APPLNO");
            sbQuery.append(", FILLINGDATE");
            sbQuery.append(", ISSUEDDATE");
            sbQuery.append(", CLASSIFICATIONS");
            sbQuery.append(", CLAIMS");
            sbQuery.append(", DESCRIPTION");
            sbQuery.append(", XMLQUERY('for $p at $i in $REFERENCETOPATENTS/ReferenceToPatents/ReferenceToPatent");
            sbQuery.append("            let $pid := (if ($i = count($REFERENCETOPATENTS/ReferenceToPatents/ReferenceToPatent)) ");
            sbQuery.append("                         then $p/text()");
            sbQuery.append("                         else (concat($p/text(),\";\")))");
            sbQuery.append("            return $pid') AS REFERENCETOPATENTS");
            sbQuery.append(" FROM ").append(dbName).append(".PATENTS");
            sbQuery.append(" WHERE PATENTNUMBER = ? ");

            stmt = conn.prepareStatement(sbQuery.toString());
            stmt.setString(1, patNumber);
            rs = stmt.executeQuery();

            if (rs.next()) {
                patent = new Patent();
                // get patentNumber;
                String patentNumber = rs.getString(Constants.PATENTNUMBER);
                if (!StringUtils.isEmpty(patentNumber)) {
                    patent.setPatNumber(patentNumber);
                }
                // get Title;
                String title = rs.getString(Constants.TITLE);
                if (!StringUtils.isEmpty(title)) {
                    patent.setPatTitle(title);
                }
                // get patentAbstract;
                String patentAbStract = rs.getString(Constants.PATENTABSTRACT);
                if (!StringUtils.isEmpty(patentAbStract)) {
                    patent.setPatAbstract(patentAbStract);
                }
                // get Inventors;
                String inventors = rs.getString(Constants.INVENTORS);
                if (!StringUtils.isEmpty(inventors)) {
                    List<String> inventorList = getInventorsFromSqlXml(inventors);
                    patent.setPatInventors(inventorList);
                }
                // get primaryExaminers & secondaryExaminers;
                String examiners = rs.getString(Constants.EXAMINERS);
                if (!StringUtils.isEmpty(examiners)) {
                    List<String> priExaminers = getPriExaminersFromSqlXml(examiners);
                    patent.setPatPriExaminers(priExaminers);
                    List<String> asstExaminers = getAsstExaminersFromSqlXml(examiners);
                    patent.setPatAsstExaminers(asstExaminers);
                }
                // get Assignees;
                String assignees = rs.getString(Constants.ASSIGNEES);
                if (!StringUtils.isEmpty(assignees)) {
                    List<String> assigneeList = getAssigneesFromSqlXml(assignees);
                    patent.setPatAssignees(assigneeList);
                }
                // get applNo;
                String applNo = rs.getString(Constants.APPPLNO);
                if (!StringUtils.isEmpty(applNo)) {
                    patent.setPatApplNo(applNo);
                }
                // get fillingDate;
                Date fillingDate = rs.getDate(Constants.FILLINGDATE);
                if (fillingDate != null) {
                    patent.setPatFiledDate(fillingDate);
                }
                // get IssuedDate;
                Date issuedDate = rs.getDate(Constants.ISSUEDDATE);
                if (issuedDate != null) {
                    patent.setPatIssuedDate(issuedDate);
                }
                // get US & International classifications;
                String classifications = rs.getString(Constants.CLASSIFICATIONS);
                if (!StringUtils.isEmpty(classifications)) {
                    List<String> usClassList = getUSClassesFromSqlXml(classifications);
                    patent.setPatUSClasses(usClassList);
                    List<String> intClassList = getIntClassesFromSqlXml(classifications);
                    patent.setPatIntClasses(intClassList);
                }
                // get referenceToPatents;
                String patentsRefToIds = rs.getString(Constants.REFERENCETOPATENTS);
                if (!StringUtils.isEmpty(patentsRefToIds)) {
                    String[] patentIds = patentsRefToIds.split(";");
                    StringBuilder sbPatentIds = new StringBuilder();
                    for (String id : patentIds) {
                        if (sbPatentIds.length() > 0) {
                            sbPatentIds.append(",");
                        }
                        sbPatentIds.append("'").append(id.trim()).append("'");
                    }
                    patent.setPatentsRefTo(getRelatedPatents(conn, sbPatentIds.toString()));
                }
                // get referenceByPatents;
                patent.setPatentsRefBy(getPatentsRefBy(conn, patNumber));
                // get Claims;
                String claims = rs.getString(Constants.CLAIMS);
                if (!StringUtils.isEmpty(claims)) {
                    List<String> claimList = getClaimsFromSqlXml(claims);
                    patent.setPatClaims(claimList);
                }
                // get description;
                String description = rs.getString(Constants.DESCRIPTION);
                if (!StringUtils.isEmpty(description)) {
                    patent.setPatDesc(description);
                }
            }
        } catch (Exception ex) {
            Logger.getLogger(PatentDaoImpl.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage());
            throw ex;
        } finally{
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return patent;
    }

    /* (non-Javadoc)
     * @see org.iuniv.adb.patent.dao.PatentDao#searchPatent(java.lang.String)
     */
    @Override
    public List<Patent> searchPatents(String query) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Patent> patentList = new ArrayList<Patent>();

        try {
            conn = DBAdapter.getConnection();
            String dbName = DBAdapter.getDBName();
            String sql = " SELECT PATENTNUMBER, TITLE FROM " + dbName + ".PATENTS "
                       + " WHERE LOWER(PATENTNUMBER) LIKE LOWER('%" + query + "%') "
                       + " OR LOWER(TITLE) LIKE LOWER('%" + query + "%') "
                       + " OR LOWER(ABSTRACT) LIKE LOWER('%" + query + "%') "
                       + " OR LOWER(DESCRIPTION) LIKE LOWER('%" + query + "%')"
                       + " OR XMLEXISTS('$" + Constants.TBL_COL_CLAIMS + "/Claims/Claim[contains(lower-case(text()), lower-case(\"" + query + "\"))]')";

            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            while (rs.next()) {
                Patent patent = new Patent();
                //get patentNumber;
                String patentNumber =  rs.getString(Constants.PATENTNUMBER);
                if(!StringUtils.isEmpty(patentNumber)){
                    patent.setPatNumber(patentNumber);
                }
                //get Title;
                String title = rs.getString(Constants.TITLE);
                if(!StringUtils.isEmpty(title)){
                    patent.setPatTitle(title);
                }
                patentList.add(patent);
            }
        } catch (Exception ex) {
            Logger.getLogger(PatentDaoImpl.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage());
            throw ex;
        } finally{
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return patentList;
    }

    /* (non-Javadoc)
     * @see org.iuniv.adb.patent.dao.PatentDao#searchPatents(java.util.Map)
     */
    @Override
    public List<Patent> searchPatents(Map<String, Object> mapAdvOpts) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Patent> patentList = null;

        try {
            conn = DBAdapter.getConnection();
            String dbName = DBAdapter.getDBName();
            String sql = "SELECT PATENTNUMBER, TITLE FROM " + dbName + ".PATENTS " + getWhereSqlForAdvSrch(mapAdvOpts);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            patentList = new ArrayList<Patent>();
            while (rs.next()) {
                Patent patent = new Patent();
                //get patentNumber;
                String patentNumber =  rs.getString(Constants.PATENTNUMBER);
                if(!StringUtils.isEmpty(patentNumber)){
                    patent.setPatNumber(patentNumber);
                }
                //get Title;
                String title = rs.getString(Constants.TITLE);
                if(!StringUtils.isEmpty(title)){
                    patent.setPatTitle(title);
                }
                patentList.add(patent);
            }
        } catch (Exception ex) {
            Logger.getLogger(PatentDaoImpl.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage());
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return patentList;
    }

    /**
     * Method to get patents that refer to the current patent
     *
     * @param conn
     * @param patNumber
     * @return
     */
    private List<Patent> getPatentsRefBy(Connection conn, String patNumber) throws Exception {
        Statement stmt = null;
        ResultSet rs = null;
        Patent patent = null;
        List<Patent> patentsRefBy = null;

        try {
            conn = DBAdapter.getConnection();
            String dbName = DBAdapter.getDBName();
            String sql = "SELECT PATENTNUMBER, TITLE, ASSIGNEES, FILLINGDATE, ISSUEDDATE FROM " + dbName + ".PATENTS "
                       + "WHERE XMLEXISTS('$REFERENCETOPATENTS/ReferenceToPatents/ReferenceToPatent[text() = \"" + patNumber + "\"]')";
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            patentsRefBy = new ArrayList<Patent>();
            while (rs.next()) {
                patent = new Patent();
                //get patentNumber;
                String patentNumber =  rs.getString(Constants.PATENTNUMBER);
                if(!StringUtils.isEmpty(patentNumber)){
                    patent.setPatNumber(patentNumber);
                }
                //get Title;
                String title = rs.getString(Constants.TITLE);
                if(!StringUtils.isEmpty(title)){
                    patent.setPatTitle(title);
                }
                //get Assignees;
                String assignees = rs.getString(Constants.ASSIGNEES);
                if(!StringUtils.isEmpty(assignees)){
                    List<String> assigneeList = getAssigneesFromSqlXml(assignees);
                    patent.setPatAssignees(assigneeList);
                }
                //get fillingDate;
                Date fillingDate = rs.getDate(Constants.FILLINGDATE);
                if(fillingDate != null){
                    patent.setPatFiledDate(fillingDate);
                }
                //get IssuedDate;
                Date issuedDate = rs.getDate(Constants.ISSUEDDATE);
                if(issuedDate != null){
                    patent.setPatIssuedDate(issuedDate);
                }
                patentsRefBy.add(patent);
            }
        } catch (Exception ex) {
            Logger.getLogger(PatentDaoImpl.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage());
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }

        return patentsRefBy;
    }

    /**
     * Method to get related patent information
     *
     * @param conn
     * @param patNumberList
     * @return
     * @throws Exception
     */
    private List<Patent> getRelatedPatents(Connection conn, String patNumberList) throws Exception {
        Statement stmt = null;
        ResultSet rs = null;
        List<Patent> patentList = null;

        try {
            conn = DBAdapter.getConnection();
            String dbName = DBAdapter.getDBName();
            String sql = "SELECT PatentNumber, Title, Assignees, FillingDate, IssuedDate "
                       + " FROM " + dbName + ".PATENTS WHERE PatentNumber IN (" + patNumberList + ")";
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            patentList = new ArrayList<Patent>();
            while (rs.next()) {
                Patent patent = new Patent();
                //get patentNumber;
                String patentNumber =  rs.getString(Constants.PATENTNUMBER);
                if(!StringUtils.isEmpty(patentNumber)){
                    patent.setPatNumber(patentNumber);
                }
                //get Title;
                String title = rs.getString(Constants.TITLE);
                if(!StringUtils.isEmpty(title)){
                    patent.setPatTitle(title);
                }
                //get Assignees;
                String assignees = rs.getString(Constants.ASSIGNEES);
                if(!StringUtils.isEmpty(assignees)){
                    List<String> assigneeList = getAssigneesFromSqlXml(assignees);
                    patent.setPatAssignees(assigneeList);
                }
                //get fillingDate;
                Date fillingDate = rs.getDate(Constants.FILLINGDATE);
                if(fillingDate != null){
                    patent.setPatFiledDate(fillingDate);
                }
                //get IssuedDate;
                Date issuedDate = rs.getDate(Constants.ISSUEDDATE);
                if(issuedDate != null){
                    patent.setPatIssuedDate(issuedDate);
                }
                patentList.add(patent);
            }
        } catch (Exception ex) {
            Logger.getLogger(PatentDaoImpl.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage());
            throw ex;
        } finally{
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return patentList;
    }

    /**
     * This method convert inventor from Result List to an array list of inventors
     *
     * @param sqlXmlData
     * @return
     * @throws ParserConfigurationException
     * @throws SAXException
     * @throws IOException
     */
    private List<String> getInventorsFromSqlXml(String sqlXmlData)
            throws ParserConfigurationException, SAXException, IOException {
        List<String> inventors = new ArrayList<String>();
        DocumentBuilder db = DocumentBuilderFactory.newInstance().newDocumentBuilder();
        InputSource is = new InputSource();
        is.setCharacterStream(new StringReader(sqlXmlData));
        Document inventorsXml = db.parse(is);
        NodeList inventorsNodeList = inventorsXml.getElementsByTagName(Constants.INVENTOR);
        for (int i = 0; i < inventorsNodeList.getLength(); i++) {
            Node node = inventorsNodeList.item(i);
            String inventorStr = node.getTextContent();
            if (!StringUtils.isEmpty(inventorStr)) {
                inventors.add(inventorStr);
            }
        }
        return inventors;
    }

    /**
     * This method convert examiner from Result List to an array list of primary examiner
     *
     * @param sqlXmlData
     * @return
     * @throws ParserConfigurationException
     * @throws SAXException
     * @throws IOException
     */
    private List<String> getPriExaminersFromSqlXml(String sqlXmlData)
            throws ParserConfigurationException, SAXException, IOException {
        List<String> primaryExaminers = new ArrayList<String>();
        DocumentBuilder db = DocumentBuilderFactory.newInstance().newDocumentBuilder();
        InputSource is = new InputSource();
        is.setCharacterStream(new StringReader(sqlXmlData));
        Document inventorsXml = db.parse(is);
        NodeList inventorsNodeList = inventorsXml.getElementsByTagName(Constants.EXAMINER);
        for (int i = 0; i < inventorsNodeList.getLength(); i++) {
            Node node = inventorsNodeList.item(i);
            String examinerType = node.getFirstChild().getTextContent();
            String examinerName = node.getLastChild().getTextContent();
            if (Constants.PRIMARYEXAMINER.equalsIgnoreCase(examinerType)) {
                primaryExaminers.add(examinerName);
            }
        }
        return primaryExaminers;
    }

    /**
     * This method convert examiner from Result List to an array list of secondary examiner
     *
     * @param sqlXmlData
     * @return
     * @throws ParserConfigurationException
     * @throws SAXException
     * @throws IOException
     */
    private List<String> getAsstExaminersFromSqlXml(String sqlXmlData)
            throws ParserConfigurationException, SAXException, IOException {
        List<String> secondaryExaminers = new ArrayList<String>();
        DocumentBuilder db = DocumentBuilderFactory.newInstance().newDocumentBuilder();
        InputSource is = new InputSource();
        is.setCharacterStream(new StringReader(sqlXmlData));
        Document inventorsXml = db.parse(is);
        NodeList inventorsNodeList = inventorsXml.getElementsByTagName(Constants.EXAMINER);
        for (int i = 0; i < inventorsNodeList.getLength(); i++) {
            Node node = inventorsNodeList.item(i);
            String examinerType = node.getFirstChild().getTextContent();
            String examinerName = node.getLastChild().getTextContent();
            if (Constants.SECONDARYEXAMINER.equalsIgnoreCase(examinerType)) {
                secondaryExaminers.add(examinerName);
            }
        }
        return secondaryExaminers;
    }

    /**
     * This method convert Assignees from Result List to an array list of Assignees
     *
     * @param sqlXmlData
     * @return
     * @throws ParserConfigurationException
     * @throws SAXException
     * @throws IOException
     */
    private List<String> getAssigneesFromSqlXml(String sqlXmlData)
            throws ParserConfigurationException, SAXException, IOException {
        List<String> inventors = new ArrayList<String>();
        DocumentBuilder db = DocumentBuilderFactory.newInstance().newDocumentBuilder();
        InputSource is = new InputSource();
        is.setCharacterStream(new StringReader(sqlXmlData));
        Document inventorsXml = db.parse(is);
        NodeList inventorsNodeList = inventorsXml.getElementsByTagName(Constants.ASSIGNEE);
        for (int i = 0; i < inventorsNodeList.getLength(); i++) {
            Node node = inventorsNodeList.item(i);
            String inventorStr = node.getTextContent();
            if (!StringUtils.isEmpty(inventorStr)) {
                inventors.add(inventorStr);
            }
        }
        return inventors;
    }

    /**
     * This method convert classifications from Result List to an array list of US classifications
     *
     * @param classificationResultSet
     * @return
     * @throws ParserConfigurationException
     * @throws SAXException
     * @throws IOException
     */
    private List<String> getUSClassesFromSqlXml(String classificationResultSet)
            throws ParserConfigurationException, SAXException, IOException {
        List<String> primaryExaminers = new ArrayList<String>();
        DocumentBuilder db = DocumentBuilderFactory.newInstance().newDocumentBuilder();
        InputSource is = new InputSource();
        is.setCharacterStream(new StringReader(classificationResultSet));
        Document inventorsXml = db.parse(is);
        NodeList inventorsNodeList = inventorsXml.getElementsByTagName(Constants.CLASSIFICATION);
        for (int i = 0; i < inventorsNodeList.getLength(); i++) {
            Node node = inventorsNodeList.item(i);
            String examinerType = node.getFirstChild().getTextContent();
            String examinerName = node.getLastChild().getTextContent();
            if (Constants.USCLASSIFICATION.equalsIgnoreCase(examinerType)) {
                primaryExaminers.add(examinerName);
            }
        }
        return primaryExaminers;
    }

    /**
     * This method convert classification from Result List to an array list of International
     * classification
     *
     * @param sqlXmlData
     * @return
     * @throws ParserConfigurationException
     * @throws SAXException
     * @throws IOException
     */
    private List<String> getIntClassesFromSqlXml(String sqlXmlData)
            throws ParserConfigurationException, SAXException, IOException {
        List<String> secondaryExaminers = new ArrayList<String>();
        DocumentBuilder db = DocumentBuilderFactory.newInstance().newDocumentBuilder();
        InputSource is = new InputSource();
        is.setCharacterStream(new StringReader(sqlXmlData));
        Document inventorsXml = db.parse(is);
        NodeList inventorsNodeList = inventorsXml.getElementsByTagName(Constants.CLASSIFICATION);
        for (int i = 0; i < inventorsNodeList.getLength(); i++) {
            Node node = inventorsNodeList.item(i);
            String examinerType = node.getFirstChild().getTextContent();
            String examinerName = node.getLastChild().getTextContent();
            if (Constants.INTERNATIONALCLASSIFICATION.equalsIgnoreCase(examinerType)) {
                secondaryExaminers.add(examinerName);
            }
        }
        return secondaryExaminers;
    }

    /**
     * This method convert reference to patents from Result List to an array list of reference to
     * patents
     *
     * @param sqlXmlData
     * @return
     * @throws ParserConfigurationException
     * @throws SAXException
     * @throws IOException
     */
    private List<String> getClaimsFromSqlXml(String sqlXmlData)
            throws ParserConfigurationException, SAXException, IOException {
        List<String> claims = new ArrayList<String>();
        DocumentBuilder db = DocumentBuilderFactory.newInstance().newDocumentBuilder();
        InputSource is = new InputSource();
        is.setCharacterStream(new StringReader(sqlXmlData));
        Document claimsXml = db.parse(is);
        NodeList claimsNodeList = claimsXml.getElementsByTagName(Constants.CLAIM);
        for (int i = 0; i < claimsNodeList.getLength(); i++) {
            Node node = claimsNodeList.item(i);
            String claimStr = node.getTextContent();
            if (!StringUtils.isEmpty(claimStr)) {
                claims.add(claimStr);
            }
        }
        return claims;
    }

    /**
     * Method to form WHERE statement from advanced options
     *
     * @param mapAdvOpts
     * @return The string of WHERE statement
     */
    private String getWhereSqlForAdvSrch(Map<String, Object> mapAdvOpts) {
        StringBuilder whereSql = new StringBuilder();
        if (mapAdvOpts != null && mapAdvOpts.size() > 0) {

            String advOpt = (String) mapAdvOpts.get(Constants.AS_PNUM);
            if (!StringUtils.isEmpty(advOpt)) {
                if (whereSql.length() == 0) {
                    whereSql.append("WHERE ");
                } else {
                    whereSql.append(" AND ");
                }
                whereSql.append("LOWER(" + Constants.TBL_COL_PNUM + ") LIKE LOWER('%" + advOpt + "%')");
            }
            advOpt = (String) mapAdvOpts.get(Constants.AS_PTITLE);
            if (!StringUtils.isEmpty(advOpt)) {
                if (whereSql.length() == 0) {
                    whereSql.append("WHERE ");
                } else {
                    whereSql.append(" AND ");
                }
                whereSql.append("LOWER(" + Constants.TBL_COL_PTITLE + ") LIKE LOWER('%" + advOpt + "%')");
            }
            advOpt = (String) mapAdvOpts.get(Constants.AS_PINVENTOR);
            if (!StringUtils.isEmpty(advOpt)) {
                if (whereSql.length() == 0) {
                    whereSql.append("WHERE ");
                } else {
                    whereSql.append(" AND ");
                }
                whereSql.append("XMLEXISTS('$" + Constants.TBL_COL_PINVENTORS + "/Inventors/Inventor[contains(lower-case(text()), lower-case(\"" + advOpt + "\"))]')");
            }
            advOpt = (String) mapAdvOpts.get(Constants.AS_PASSIGNEE);
            if (!StringUtils.isEmpty(advOpt)) {
                if (whereSql.length() == 0) {
                    whereSql.append("WHERE ");
                } else {
                    whereSql.append(" AND ");
                }
                whereSql.append("XMLEXISTS('$" + Constants.TBL_COL_PASSIGNEES + "/Assignees/Assignee[contains(lower-case(text()), lower-case(\"" + advOpt + "\"))]')");
            }
            advOpt = (String) mapAdvOpts.get(Constants.AS_PUSCLS);
            if (!StringUtils.isEmpty(advOpt)) {
                if (whereSql.length() == 0) {
                    whereSql.append("WHERE ");
                } else {
                    whereSql.append(" AND ");
                }
                whereSql.append("XMLEXISTS('$" + Constants.TBL_COL_PCLS + "/Classifications/Classification[ClassificationType=\"1\" and ClassificationValue=\"" + advOpt + "\"]')");
            }
            advOpt = (String) mapAdvOpts.get(Constants.AS_PINTLCLS);
            if (!StringUtils.isEmpty(advOpt)) {
                if (whereSql.length() == 0) {
                    whereSql.append("WHERE ");
                } else {
                    whereSql.append(" AND ");
                }
                whereSql.append("XMLEXISTS('$" + Constants.TBL_COL_PCLS + "/Classifications/Classification[ClassificationType=\"2\" and ClassificationValue=\"" + advOpt + "\"]')");
            }
        }
        return whereSql.toString();
    }
}